package com.cn.jdia.tas.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.joda.time.DateTime;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.cn.jdia.tas.utils.DBManager4Oracle;
import com.cn.jdia.tas.utils.DBManager4Oracle2;
import com.cn.jdia.tas.utils.DBManager4SqlServer;
import com.google.gson.Gson;

@Controller
@RequestMapping("/control")
public class QuotaController {

	@RequestMapping("/quota")
	public String getQuotaData(HttpServletRequest request, HttpServletResponse response) throws IOException {
		response.setHeader("Access-Control-Allow-Origin", "*");
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();

		DBManager4Oracle2 db = DBManager4Oracle2.createInstance();
		// DBManager4SqlServer db = DBManager4SqlServer.createInstance();
		db.connectDB();

		Gson gson = new Gson();

		DateTime datetime = new DateTime();
		String nowDate = datetime.toString("YYYY-MM-dd");
		String hajime = datetime.toString("YYYY") + "-01-01";

		String yesterday = datetime.minusDays(1).toString("YYYY-MM-dd");

		String last = datetime.minusYears(1).toString("YYYY-MM-dd");
		String lasthajime = datetime.minusYears(1).toString("YYYY") + "-01-01";

		Map<String, Object> map = new HashMap<String, Object>();
		try {
			StringBuffer sb = new StringBuffer(
					"select sum(field0002) jiaci,sum(field0003) lvke,sum(field0004) huoyou from formmain_3095 where field0001>=to_date('")
							.append(hajime).append("','YYYY-MM-dd') and field0001<=to_date('").append(nowDate).append("','YYYY-MM-dd')");
			ResultSet rs = db.executeQuery(sb.toString());

			int jiaci = 0;
			int lvke = 0;
			int huoyou = 0;

			while (rs.next()) {
				jiaci = rs.getInt("jiaci");
				lvke = rs.getInt("lvke");
				huoyou = rs.getInt("huoyou");
			}

			rs.close();

			StringBuffer sb2 = new StringBuffer(
					"select sum(field0002) jiaci,sum(field0003) lvke,sum(field0004) huoyou from formmain_3095 where field0001>=to_date('")
							.append(lasthajime).append("','YYYY-MM-dd') and field0001<=to_date('").append(last).append("','YYYY-MM-dd')");
			
//			System.out.println(sb2.toString());
			
			ResultSet rs2 = db.executeQuery(sb2.toString());

			int l_jiaci = 0;
			int l_lvke = 0;
			int l_huoyou = 0;

			while (rs2.next()) {
				l_jiaci = rs2.getInt("jiaci");
				l_lvke = rs2.getInt("lvke");
				l_huoyou = rs2.getInt("huoyou");
			}

			rs2.close();

			map.put("jiaci", jiaci);
			map.put("lvke", lvke);
			map.put("huoyou", huoyou);

			map.put("l_jiaci", l_jiaci);
			map.put("l_lvke", l_lvke);
			map.put("l_huoyou", l_huoyou);

			// StringBuffer sb3 = new StringBuffer(
			// "select field0002 jiaci,field0003 lvke,field0004 huoyou from
			// dbo.formmain_3095 where CONVERT(varchar(10), field0001, 21) like
			// '")
			// .append(yesterday).append("%'");
			// ResultSet rs3 = db.executeQuery(sb3.toString());
			// int n_jiaci=0;
			// int n_lvke = 0;
			// int n_huoyou = 0;
			//
			// while (rs3.next()) {
			// n_jiaci = rs3.getInt("jiaci");
			// n_lvke = rs3.getInt("lvke");
			// n_huoyou = rs3.getInt("huoyou");
			// }

			// map.put("n_jiaci", n_jiaci);
			// map.put("n_lvke", n_lvke);
			// map.put("n_huoyou", n_huoyou);

		} catch (Exception e) {
			e.printStackTrace();
		}

		db.closeDB();

		DBManager4Oracle dbo = DBManager4Oracle.createInstance();
		dbo.connectDB();

		StringBuffer sb = new StringBuffer(
				"select FIELD0002,FIELD0003,FIELD0004,FIELD0007 from FORMMAIN_6250_AOC WHERE to_char(MODIFY_DATE,'YYYY-MM-DD') like '%");
		sb.append(nowDate).append("%'");

		try {
			ResultSet rs3 = dbo.executeQuery(sb.toString());
			int n_jiaci = 0;
			int n_lvke = 0;
			int n_huoyou = 0;
			String n_fangxing = "";
			while (rs3.next()) {
				n_jiaci = rs3.getInt("FIELD0002");
				n_lvke = rs3.getInt("FIELD0003");
				n_huoyou = rs3.getInt("FIELD0004");
				n_fangxing = rs3.getString("FIELD0007");
			}

			map.put("n_jiaci", n_jiaci);
			map.put("n_lvke", n_lvke);
			map.put("n_huoyou", n_huoyou);
			map.put("n_fangxing", n_fangxing);

			rs3.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		dbo.closeDB();

		String ret = gson.toJson(map);
		System.out.println(ret);

		out.write(ret);
		out.flush();
		out.close();

		return null;
	}

}
